/*DB Scripts for MS SQL*/

/*Album*/
CREATE TABLE [mb_Albums](
[PKID] integer identity PRIMARY KEY
,[Title] varchar(50) NOT NULL
,[Description] varchar(255) DEFAULT ''
,[Author] varchar(500) NOT NULL
,[Slug] varchar(50) DEFAULT ''
,[Hits] integer DEFAULT 0
,[IsPublic] bit
,[IsPublished] bit
,[IsCommentEnabled] bit
,[DateCreated] smalldatetime DEFAULT getdate()
,[DateModified] smalldatetime DEFAULT getdate()
  
);
CREATE TABLE [mb_PortalFiles](
[PKID] integer identity PRIMARY KEY
,[Name] varchar(50) DEFAULT ''
,[Url] varchar(50) NOT NULL
,[Hits] integer DEFAULT 0
,[Description] varchar(500) DEFAULT ''
  
);
CREATE TABLE [mb_AlbumComment] (
    [PKID] integer identity PRIMARY KEY,
    [AlbumID] integer NOT NULL,
    [CommentDate] smalldatetime DEFAULT getdate(),
    [Author] varchar(50),
    [Email] varchar(50),
    [Website] varchar(50),
    [Comment] varchar(200) NOT NULL,
    [Country] varchar(50),
    [IP] varchar(50),
    [IsApproved] bit,
    CONSTRAINT [FK_mb_AlbumComment_AlbumID_mb_Albums_PKID] FOREIGN KEY ([AlbumID]) REFERENCES [mb_Albums] ([PKID])
);
CREATE TABLE [mb_AlbumFile] (
    [AlbumID] integer NOT NULL,
    [FileID] integer NOT NULL,
    CONSTRAINT [FK_mb_AlbumFile_AlbumID_mb_Albums_PKID] FOREIGN KEY ([AlbumID]) REFERENCES [mb_Albums] ([PKID]),
    CONSTRAINT [FK_mb_AlbumFile_FileID_mb_PortalFiles_PKID] FOREIGN KEY ([FileID]) REFERENCES [mb_PortalFiles] ([PKID])
);

CREATE TABLE [mb_AlbumTag] (
    [AlbumID] integer NOT NULL,
    [Tag] varchar(50) NOT NULL,
    CONSTRAINT [FK_mb_AlbumTag_0] FOREIGN KEY ([AlbumID]) REFERENCES [mb_Albums] ([PKID])
);

/*===========================================Category====================================*/
CREATE TABLE [mb_Categories] (
    [CategoryID] integer identity PRIMARY KEY,
    [CategoryName] varchar(50),
    [Description] varchar(100),
    [Slug] varchar(50) DEFAULT '',
    [Icon] varchar(50) DEFAULT '',
    [ParentID] integer NOT NULL,
    [DisplayOrder] integer DEFAULT 0,
    CONSTRAINT [FK_mb_Categories_ParentID_mb_Categories_CategoryID] FOREIGN KEY ([ParentID]) REFERENCES [mb_Categories] ([CategoryID])
);
/*Community*/
CREATE TABLE [mb_Communities] (
    [PKID] integer identity PRIMARY KEY,
    [Name] varchar(255) NOT NULL,
    [Description] text DEFAULT '',
    [Slug] varchar(50) DEFAULT '',
    [Icon] varchar(50) DEFAULT '',
    [ParentID] integer,
    CONSTRAINT [FK_mb_Communities_ParentID_mb_Communities_PKID] FOREIGN KEY ([ParentID]) REFERENCES [mb_Communities] ([PKID])
);
CREATE TABLE [mb_Topics](
[TopicID] integer identity PRIMARY KEY
,[Title] varchar(255) NOT NULL
,[Description] text DEFAULT ''
,[Content] text NOT NULL
,[DateCreated] smalldatetime DEFAULT getdate()
,[DateModified] smalldatetime DEFAULT getdate()
,[Author] varchar(500)
,[IsPublished] bit
,[IsCommentEnabled] bit
,[IsValuable] bit
,[IsApproved] bit
,[Icon] varchar(50) DEFAULT ''
  
);
CREATE TABLE [mb_CommunityTopic] (
    [CommunityID] integer NOT NULL,
    [TopicID] integer NOT NULL,
    CONSTRAINT [FK_mb_CommunityTopic_CommunityID_mb_Communities_PKID] FOREIGN KEY ([CommunityID]) REFERENCES [mb_Communities] ([PKID]),
    CONSTRAINT [FK_mb_CommunityTopic_TopicID_mb_Topics_TopicID] FOREIGN KEY ([TopicID]) REFERENCES [mb_Topics] ([TopicID])
);
CREATE TABLE [mb_DataStoreSettings] (
[ExtensionType] VARCHAR(50)  NOT NULL,
[ExtensionId] VARCHAR(100)  NOT NULL,
[Settings] TEXT  NULL
);
CREATE TABLE [mb_Pages](
[PageID] integer identity PRIMARY KEY
,[Title] VARCHAR(255) NOT NULL
,[Description] TEXT
,[PageContent] TEXT
,[Keywords] VARCHAR(255)
,[DateCreated] smalldatetime DEFAULT getdate()
,[DateModified] smalldatetime DEFAULT getdate()
,[IsPublished] bit
,[IsFrontPage] bit
,[Parent] integer DEFAULT -1
,[ShowInList] bit
,[Attachments] text DEFAULT ''
,[Slug] varchar(255) DEFAULT ''
  
);

CREATE TABLE [mb_PingService] (
[PingServiceID] INTEGER  identity PRIMARY KEY,
[Link] VARCHAR(255)  NULL
);
CREATE TABLE [mb_Posts](
[PostID] integer identity PRIMARY KEY
,[Title] VARCHAR(255) NOT NULL
,[Description] TEXT
,[PostContent] TEXT
,[DateCreated] smalldatetime DEFAULT getdate()
,[DateModified] smalldatetime DEFAULT getdate()
,[Author] VARCHAR(50)
,[IsPublished] bit
,[IsCommentEnabled] bit
,[Raters] INTEGER DEFAULT 0
,[Rating] float DEFAULT 0
,[Slug] VARCHAR(255)
,[Attachments] text DEFAULT ''
  
);
CREATE TABLE [mb_PostCategory] (
    [PostID] integer NOT NULL,
    [CategoryID] integer NOT NULL,
    CONSTRAINT [FK_mb_PostCategory_PostID_mb_Posts_PostID] FOREIGN KEY ([PostID]) REFERENCES [mb_Posts] ([PostID]),
    CONSTRAINT [FK_mb_PostCategory_CategoryID_mb_Categories_CategoryID] FOREIGN KEY ([CategoryID]) REFERENCES [mb_Categories] ([CategoryID])
);
CREATE TABLE [mb_PostComment] (
    [PKID] integer identity PRIMARY KEY,
    [PostID] integer NOT NULL,
    [CommentDate] smalldatetime DEFAULT getdate(),
    [Author] varchar(255),
    [Email] varchar(255),
    [Website] varchar(255),
    [Comment] text,
    [Country] varchar(255),
    [IP] varchar(50),
    [IsApproved] bit,
    CONSTRAINT [FK_mb_PostComment_PostID_mb_Posts_PostID] FOREIGN KEY ([PostID]) REFERENCES [mb_Posts] ([PostID])
);
CREATE TABLE [mb_PostNotify] (
    [PostID] integer NOT NULL,
    [NotifyAddress] varchar(255) NOT NULL,
    CONSTRAINT [FK_mb_PostNotify_0] FOREIGN KEY ([PostID]) REFERENCES [mb_Posts] ([PostID])
);
CREATE TABLE [mb_PostTag] (
    [PostID] integer NOT NULL,
    [Tag] varchar(100) NOT NULL,
    CONSTRAINT [FK_mb_PostTag_PostID_mb_Posts_PostID] FOREIGN KEY ([PostID]) REFERENCES [mb_Posts] ([PostID])
);
CREATE TABLE [mb_Profiles] (
[ProfileID] INTEGER  identity PRIMARY KEY,
[UserName] VARCHAR(100)  NOT NULL,
[SettingName] VARCHAR(200)  NOT NULL,
[SettingValue] TEXT  NULL
);
CREATE TABLE [mb_Rates](
[PKID] integer identity PRIMARY KEY
,[Title] varchar(255) NOT NULL
,[Memo] varchar(500) DEFAULT ''
,[Rating] float DEFAULT 1.0
,[Raters] text DEFAULT ''
,[Hits] integer DEFAULT 0
  
);
CREATE TABLE [mb_Votes](
[PKID] INTEger identity PRIMARY KEY
,[Title] varchar(50) NOT NULL
,[Description] varchar(255) DEFAULT ''
,[Author] varchar(50)
,[DateCreated] smalldatetime DEFAULT getdate()
,[DateModified] smalldatetime DEFAULT getdate()
,[IsPublished] bit
,[IsMultiple] bit
  
);
CREATE TABLE [mb_VoteItem] (
    [VoteID] integer NOT NULL,
    [RateID] integer NOT NULL,
    CONSTRAINT [FK_mb_VoteItem_VoteID_mb_Votes_PKID] FOREIGN KEY ([VoteID]) REFERENCES [mb_Votes] ([PKID]),
    CONSTRAINT [FK_mb_VoteItem_RateID_mb_Rates_PKID] FOREIGN KEY ([RateID]) REFERENCES [mb_Rates] ([PKID])
);
CREATE TABLE [mb_Roles] (
[RoleID] INTEGER  identity PRIMARY KEY,
[Role] VARCHAR(100)  NOT NULL
);
CREATE TABLE [mb_Settings] (
[SettingName] VARCHAR(50)  PRIMARY KEY,
[SettingValue] TEXT  NULL
);
CREATE TABLE [mb_StopWords] (
[StopWord] VARCHAR(50) PRIMARY KEY
);

CREATE TABLE [mb_TopicComment] (
    [PKID] integer identity PRIMARY KEY,
    [TopicID] integer NOT NULL,
    [CommentDate] smalldatetime DEFAULT getdate(),
    [Author] varchar(50),
    [Email] varchar(50) DEFAULT '',
    [Website] varchar(50) DEFAULT '',
    [Comment] text DEFAULT '',
    [Country] varchar(50) DEFAULT '',
    [IP] varchar(50) DEFAULT '',
    [IsApproved] bit,
    CONSTRAINT [FK_mb_TopicComment_TopicID_mb_Topics_TopicID] FOREIGN KEY ([TopicID]) REFERENCES [mb_Topics] ([TopicID])
);

CREATE TABLE [mb_TopicTag] (
    [TopicID] integer NOT NULL,
    [Tag] varchar(25) NOT NULL,
    CONSTRAINT [FK_mb_TopicTag_TopicID_mb_Topics_TopicID] FOREIGN KEY ([TopicID]) REFERENCES [mb_Topics] ([TopicID])
);
CREATE TABLE [mb_Users] (
[UserID] INTEGER  identity PRIMARY KEY,
[UserName] VARCHAR(100)  NOT NULL,
[Password] VARCHAR(255)  NULL,
[LastLoginTime] smalldatetime DEFAULT getdate(),
[EmailAddress] VARCHAR(255)  NULL
);
CREATE TABLE [mb_UserRoles] (
    [UserID] integer NOT NULL,
    [RoleID] integer NOT NULL,
    CONSTRAINT [FK_mb_UserRoles_UserID_mb_Users_UserID] FOREIGN KEY ([UserID]) REFERENCES [mb_Users] ([UserID]),
    CONSTRAINT [FK_mb_UserRoles_RoleID_mb_Roles_RoleID] FOREIGN KEY ([RoleID]) REFERENCES [mb_Roles] ([RoleID])
);


